const db = require('../config/db');
const Report = require('../models/Report');

class ReportController {
  // Generate a financial report
  static async generateReport(req, res) {
    try {
      const user_id = req.userId; // Set by auth middleware
      const { type, start_date, end_date } = req.query;

      // Validate required parameters
      if (!type) {
        return res.status(400).json({ error: '报表类型是必填项' });
      }

      // Validate date format if provided
      let startDate, endDate;
      if (start_date) {
        startDate = new Date(start_date);
        if (isNaN(startDate.getTime())) {
          return res.status(400).json({ error: '开始日期格式无效' });
        }
      } else {
        // Default to 30 days ago
        startDate = new Date();
        startDate.setDate(startDate.getDate() - 30);
      }

      if (end_date) {
        endDate = new Date(end_date);
        if (isNaN(endDate.getTime())) {
          return res.status(400).json({ error: '结束日期格式无效' });
        }
      } else {
        // Default to today
        endDate = new Date();
      }

      // Ensure end date is not before start date
      if (endDate < startDate) {
        return res.status(400).json({ error: '结束日期不能早于开始日期' });
      }

      let reportData;
      switch (type) {
        case 'summary':
          reportData = await ReportController.generateSummaryReport(user_id, startDate, endDate);
          break;
        case 'category':
          reportData = await ReportController.generateCategoryReport(user_id, startDate, endDate);
          break;
        case 'trend':
          reportData = await ReportController.generateTrendReport(user_id, startDate, endDate);
          break;
        default:
          return res.status(400).json({ error: '不支持的报表类型' });
      }

      // Save report to database
      const result = await db.query(
        `INSERT INTO reports (user_id, type, data, period_start, period_end) 
         VALUES ($1, $2, $3, $4, $5) 
         RETURNING *`,
        [user_id, type, reportData, startDate, endDate]
      );

      const report = Report.fromRow(result.rows[0]);

      res.status(201).json({
        message: '报表生成成功',
        report: report
      });
    } catch (error) {
      console.error('Generate report error:', error);
      res.status(500).json({ error: '内部服务器错误' });
    }
  }

  // Get all reports for a user
  static async getReports(req, res) {
    try {
      const user_id = req.userId; // Set by auth middleware
      const { limit = 10, offset = 0 } = req.query;

      const result = await db.query(
        `SELECT * FROM reports 
         WHERE user_id = $1 
         ORDER BY generated_at DESC 
         LIMIT $2 OFFSET $3`,
        [user_id, limit, offset]
      );

      const reports = result.rows.map(row => Report.fromRow(row));

      res.status(200).json({
        reports: reports
      });
    } catch (error) {
      console.error('Get reports error:', error);
      res.status(500).json({ error: '内部服务器错误' });
    }
  }

  // Get a specific report by ID
  static async getReportById(req, res) {
    try {
      const user_id = req.userId; // Set by auth middleware
      const { id } = req.params;

      const result = await db.query(
        `SELECT * FROM reports 
         WHERE id = $1 AND user_id = $2`,
        [id, user_id]
      );

      if (result.rows.length === 0) {
        return res.status(404).json({ error: '报表未找到' });
      }

      const report = Report.fromRow(result.rows[0]);

      res.status(200).json({
        report: report
      });
    } catch (error) {
      console.error('Get report error:', error);
      res.status(500).json({ error: '内部服务器错误' });
    }
  }

  // Delete a report
  static async deleteReport(req, res) {
    try {
      const user_id = req.userId; // Set by auth middleware
      const { id } = req.params;

      const result = await db.query(
        `DELETE FROM reports 
         WHERE id = $1 AND user_id = $2 
         RETURNING *`,
        [id, user_id]
      );

      if (result.rows.length === 0) {
        return res.status(404).json({ error: '报表未找到' });
      }

      res.status(200).json({
        message: '报表删除成功'
      });
    } catch (error) {
      console.error('Delete report error:', error);
      res.status(500).json({ error: '内部服务器错误' });
    }
  }

  // Private method: Generate summary report
  static async generateSummaryReport(user_id, start_date, end_date) {
    try {
      // Get income and expense totals
      const totalsResult = await db.query(
        `SELECT 
           SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as total_income,
           SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as total_expense
         FROM bills 
         WHERE user_id = $1 
           AND bill_date >= $2 
           AND bill_date <= $3`,
        [user_id, start_date, end_date]
      );

      const totals = totalsResult.rows[0];

      // Get top expense categories
      const topCategoriesResult = await db.query(
        `SELECT 
           c.name as category_name,
           SUM(b.amount) as total_amount
         FROM bills b
         JOIN categories c ON b.category_id = c.id
         WHERE b.user_id = $1 
           AND b.type = 'expense'
           AND b.bill_date >= $2 
           AND b.bill_date <= $3
         GROUP BY c.name
         ORDER BY total_amount DESC
         LIMIT 5`,
        [user_id, start_date, end_date]
      );

      // Get account balances
      const accountBalancesResult = await db.query(
        `SELECT 
           name,
           type,
           balance
         FROM accounts 
         WHERE user_id = $1 
           AND is_active = true
         ORDER BY type, name`,
        [user_id]
      );

      return {
        period: {
          start_date: start_date,
          end_date: end_date
        },
        summary: {
          total_income: parseFloat(totals.total_income) || 0,
          total_expense: parseFloat(totals.total_expense) || 0,
          net_balance: (parseFloat(totals.total_income) || 0) - (parseFloat(totals.total_expense) || 0)
        },
        top_expense_categories: topCategoriesResult.rows,
        account_balances: accountBalancesResult.rows
      };
    } catch (error) {
      console.error('Generate summary report error:', error);
      throw error;
    }
  }

  // Private method: Generate category report
  static async generateCategoryReport(user_id, start_date, end_date) {
    try {
      // Get income by category
      const incomeByCategoryResult = await db.query(
        `SELECT 
           c.name as category_name,
           SUM(b.amount) as total_amount
         FROM bills b
         JOIN categories c ON b.category_id = c.id
         WHERE b.user_id = $1 
           AND b.type = 'income'
           AND b.bill_date >= $2 
           AND b.bill_date <= $3
         GROUP BY c.name
         ORDER BY total_amount DESC`,
        [user_id, start_date, end_date]
      );

      // Get expenses by category
      const expensesByCategoryResult = await db.query(
        `SELECT 
           c.name as category_name,
           SUM(b.amount) as total_amount
         FROM bills b
         JOIN categories c ON b.category_id = c.id
         WHERE b.user_id = $1 
           AND b.type = 'expense'
           AND b.bill_date >= $2 
           AND b.bill_date <= $3
         GROUP BY c.name
         ORDER BY total_amount DESC`,
        [user_id, start_date, end_date]
      );

      return {
        period: {
          start_date: start_date,
          end_date: end_date
        },
        income_by_category: incomeByCategoryResult.rows,
        expenses_by_category: expensesByCategoryResult.rows
      };
    } catch (error) {
      console.error('Generate category report error:', error);
      throw error;
    }
  }

  // Private method: Generate trend report
  static async generateTrendReport(user_id, start_date, end_date) {
    try {
      // Get daily income and expense trends
      const dailyTrendResult = await db.query(
        `SELECT 
           DATE(bill_date) as date,
           SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as daily_income,
           SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as daily_expense
         FROM bills 
         WHERE user_id = $1 
           AND bill_date >= $2 
           AND bill_date <= $3
         GROUP BY DATE(bill_date)
         ORDER BY date`,
        [user_id, start_date, end_date]
      );

      // Get monthly summary
      const monthlySummaryResult = await db.query(
        `SELECT 
           DATE_TRUNC('month', bill_date) as month,
           SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as monthly_income,
           SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as monthly_expense
         FROM bills 
         WHERE user_id = $1 
           AND bill_date >= $2 
           AND bill_date <= $3
         GROUP BY DATE_TRUNC('month', bill_date)
         ORDER BY month`,
        [user_id, start_date, end_date]
      );

      return {
        period: {
          start_date: start_date,
          end_date: end_date
        },
        daily_trend: dailyTrendResult.rows,
        monthly_summary: monthlySummaryResult.rows
      };
    } catch (error) {
      console.error('Generate trend report error:', error);
      throw error;
    }
  }
}

module.exports = ReportController;